SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetQuestionnaires]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetQuestionnaires]
GO

/***************************************************************************
* Name:			GetQuestionnaires
* Purpose:		Returns all the questionnaires
***************************************************************************/
CREATE PROCEDURE [dbo].[GetQuestionnaires] 
(
	@moduleType INT
)
AS

Select Q.[ID], Q.[Name], Q.[Description], Q.[Type], (Select RefMeaning from RefCode where RefCode = type) as TypeMeaning, Q.Category, 
(Select RefMeaning from RefCode where RefCode = category) as CategoryMeaning, Q.CategorySequenceNo, Q.AllowMultiple, 
CAST(ISNULL(QV.Active, 0) AS BIT) as Active, Q.ModuleType
FROM Questionnaire Q LEFT JOIN QuestionnaireVersion QV ON (Q.[Id] = QV.QuestionnaireId AND QV.Active=1)
WHERE Q.ModuleType = @moduleType

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO